package com.servicesDirectory.dao.lookup;

import com.servicesDirectory.common.exception.FunctionalException;
import com.servicesDirectory.common.exception.SystemException;
import com.servicesDirectory.common.vo.AddressVO;
import com.servicesDirectory.common.vo.BusinessVO;
import java.sql.*;
import java.util.LinkedList;
import java.util.List;

public class BaseLookupDAOImpl implements BaseLookupDAO {

    protected static Connection conn = null;

    protected static void initConnection()
            throws SQLException, ClassNotFoundException {

        if (conn == null) {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            conn = DriverManager.getConnection("jdbc:odbc:MockPJ_ServiceDirectory", "sa", "12345678");
        }
    }

    public List<AddressVO> addressLookup(AddressVO address) throws FunctionalException, SystemException {
        conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            /*
             * Initialize DB connection
             */
            initConnection();
            
            /*
             * Retrieve data from DB
             */
            
                String strQuery = "SELECT AddressID,AddressName,PostCode,TownName,CountyName,CountryName "
                        + "FROM Address,Town,County,Country "
                        + "WHERE Address.TownID = Town.TownID "
                        + "AND Address.CountyID = County.CountyID "
                        + "AND Address.CountryID = Country.CountryID";
                if (address.getPostCode().length() > 0) {
                    strQuery += " AND PostCode LIKE '" + address.getPostCode() + "%'";
                }
                if (address.getAddressName().length() > 0) {
                    strQuery += " AND AddressName LIKE '%" + address.getAddressName() + "%'";
                }
                if (address.getTownName().length() > 0) {
                    strQuery += " AND TownName LIKE '" + address.getTownName() + "%'";
                }
                stmt = conn.createStatement();
                rs = stmt.executeQuery(strQuery);

                List<AddressVO> result = new LinkedList<AddressVO>();
                AddressVO resultItem;
                
                //add data to list
                while (rs.next()) {
                    resultItem = new AddressVO();         
                    resultItem.setAddressID(rs.getInt("AddressID"));
                    resultItem.setAddressName(rs.getString("AddressName"));
                    resultItem.setPostCode(rs.getString("PostCode"));
                    resultItem.setTownName(rs.getString("TownName"));
                    resultItem.setCountyName(rs.getString("CountyName"));
                    resultItem.setCountryName(rs.getString("CountryName"));                    
                    result.add(resultItem);
                }

                return result;
            
        } catch (SQLException ex) {
        } catch (ClassNotFoundException ex) {
        } finally {
            //close resultset, statement, connection
            try {
                if (rs != null) {

                    rs.close();
                }

                if (stmt != null) {

                    stmt.close();

                }
                if (conn != null) {

                    conn.close();

                }
            } catch (SQLException e) {
            }

        }
        return null;
    }

    public List<BusinessVO> businessLookup(BusinessVO business) throws FunctionalException, SystemException {
        conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            /*
             * Initialize DB connection
             */
            initConnection();
            
            /*
             * Retrieve data from DB
             */
            
                String strQuery = "SELECT TypeOfBusinessID,BusinessName,SICCode "
                        + "FROM TypeOfBusiness "
                        + "WHERE 'a' = 'a'";
                if (business.getBusinessName().length() > 0) {
                    strQuery += " AND BusinessName LIKE '%" + business.getBusinessName() + "%'";
                }
                if (business.getSicCode().length() > 0) {
                    strQuery += " AND SICCode LIKE '" + business.getSicCode() + "%'";
                }
                
                stmt = conn.createStatement();
                rs = stmt.executeQuery(strQuery);

                List<BusinessVO> result = new LinkedList<BusinessVO>();
                BusinessVO resultItem;
                
                //add data to list
                while (rs.next()) {
                    resultItem = new BusinessVO();    
                    resultItem.setTypeOfBusinessID(rs.getInt("TypeOfBusinessID"));
                    resultItem.setBusinessName(rs.getString("BusinessName"));
                    resultItem.setSicCode(rs.getString("SICCode"));
                  
                    result.add(resultItem);
                }

                return result;
            
        } catch (SQLException ex) {
        } catch (ClassNotFoundException ex) {
        } finally {
            //close resultset, statement, connection
            try {
                if (rs != null) {

                    rs.close();
                }

                if (stmt != null) {

                    stmt.close();

                }
                if (conn != null) {

                    conn.close();

                }
            } catch (SQLException e) {
            }

        }
        return null;
    }
}
